package saeildamil.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import saeildamil.common.Manager;
import saeildamil.common.Student;

public class DbStdManager implements Manager {
	Connection conn;
	
	public DbStdManager() {
		init();
		showMenu();
	}
	
	private void showMenu() {
		int num=0;
		String message = "1.입력 2.삭제 3.수정 4.검색 5.출력 6.종료";
		do{
			num = Integer.parseInt(JOptionPane.showInputDialog(message).trim());
			switch (num) {
			case 1:
				insertStd();
				break;
			case 2:
				deleteStd();
				break;
			case 3:
				updateStd();
				break;
			case 4:
				Student std = (Student) searchStd();
				JOptionPane.showMessageDialog(null, std);
				break;
			case 5:
				printList();
				break;
			default:
				outputfile();
				break;
			}
		}while(num!=6);
	}
	@Override
	public void init() {
		BufferedReader bis=null;
		File file = new File("dbinfo.con");
		try {
			bis = new BufferedReader(new FileReader(file));
			String className = bis.readLine();
			String url = bis.readLine();
			String user= bis.readLine();
			String password = bis.readLine();
			Class.forName(className);
			conn = DriverManager.getConnection(url, user, password);
			JOptionPane.showMessageDialog(null, "연결 성공");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {bis.close();}catch(Exception e){}
		}
	}

	@Override
	public void insertStd() {
		PreparedStatement pstmt = null;
		String sql = "insert into student (stdnum, stdname, korscore, engscore, mathscore) " +
				"values (?,?,?,?,?);";
		String stdnum = JOptionPane.showInputDialog("학번").trim();
		String stdname = JOptionPane.showInputDialog("성명").trim();
		int korScore = Integer.parseInt(JOptionPane.showInputDialog("국어점수").trim());
		int engScore = Integer.parseInt(JOptionPane.showInputDialog("영어점수").trim());
		int mathScore = Integer.parseInt(JOptionPane.showInputDialog("수학점수").trim());
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, stdnum);
			pstmt.setString(2, stdname);
			pstmt.setInt(3, korScore);
			pstmt.setInt(4, engScore);
			pstmt.setInt(5, mathScore);
			
			int result = pstmt.executeUpdate();
			if (result == 1) JOptionPane.showMessageDialog(null, "학생 정보 추가");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try { pstmt.close();}catch(Exception e){}
		}
	}

	@Override
	public void deleteStd() {
		PreparedStatement pstmt=null;
		String stdname=JOptionPane.showInputDialog("삭제할 학생의 이름을 입력하세요.").trim();
		String sql="delete from student where stdname=?;";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, stdname);
			int result=pstmt.executeUpdate();
			if(result==1){
				JOptionPane.showMessageDialog(null, "해당 학생 정보 삭제");
			}else{
				JOptionPane.showMessageDialog(null, "해당 학생이 존재하지 않습니다.");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	@Override
	public void updateStd() {
		PreparedStatement pstmt=null;
		String sql=null;
		String stdName=JOptionPane.showInputDialog("수정할 학생의 이름을 입력하세요");
		int subject=Integer.parseInt(JOptionPane.showInputDialog("수정할 과목을 선택하세요 1.국어  2.수학  3.영어"));
		String score=JOptionPane.showInputDialog("새로운 점수를 입력하세요");
		switch (subject) {
		case 1:
			sql="update student set korscore=? where stdname=?;";
			break;
		case 2:
			sql="update student set mathscore=? where stdname=?;";
			break;
		default:
			sql="update student set engscore=? where stdname=?;";
			break;
		}
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, score);
			pstmt.setString(2, stdName);
			int result=pstmt.executeUpdate();
			if(result==1){
				JOptionPane.showMessageDialog(null, "해당 학생정보 수정 완료");
			} else{
				JOptionPane.showMessageDialog(null, "잘못 입력하였습니다.");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public Object searchStd() {
		Student std = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String stdNum = null;
		String stdName = null;
		int korScore = 0;
		int engScore = 0;
		int mathScore = 0;

		String name = JOptionPane.showInputDialog("검색할 학생의 이름을 입력하세요").trim(); 
		String sql = "select stdnum,stdname,korscore,engscore,mathscore from student where stdname = ?;";

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);

			rs = pstmt.executeQuery();
			rs.next();
			stdNum = rs.getString("stdnum");
			stdName = rs.getString("stdname");
			korScore = rs.getInt("korscore");
			engScore = rs.getInt("engscore");
			mathScore = rs.getInt("mathscore");
			std = new Student(stdNum, stdName, korScore, engScore, mathScore);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return std;
	}



	@Override
	public void printList() {
		String sql = "select num, stdnum, stdname, korscore, engscore, mathscore from student;";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Student std = null;
		ArrayList<Student> stdList = new ArrayList<>();
		
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				std = new Student(rs.getString("stdnum"), rs.getString("stdname"), rs.getInt("korscore"), rs.getInt("engscore"),rs.getInt("mathscore"));
				stdList.add(std);
			}
			StringBuilder sb = new StringBuilder();
			for(Student obj: stdList){
				sb.append(obj.toString()+"\n");
			}
			JOptionPane.showMessageDialog(null, sb);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}

	@Override
	public void outputfile() {
		try {
			if (!conn.isClosed()){
				conn.close();
				JOptionPane.showMessageDialog(null, "DB연결 종료");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn = null;
		}
	}

	@Override
	public void inputfile() {
		// TODO Auto-generated method stub

	}

}
